import string
import logging
from common import excelUtils
from common import dbUtils
import time
import calendar
import datetime
import copy

logger = logging.getLogger('applog')


# 处理打卡机数据
def handleCheckData(file):
    try:
        utils = excelUtils.excelTools()
        res = utils.loadCheckExcel(file)
        valuesList = []
        count = 0
        for i in range(res.__len__()):
            count += 1
            str = dataFormat(res[i][2])
            res[i].append(str)
            res[i].append(None)
            valuesList.append(tuple(res[i]))

        dbTools = dbUtils.dbTools()
        # 清除历史打卡数据
        dbTools.clear('t_checkdata')
        # 插入数据库
        sql = "INSERT INTO t_checkdata(name,checkNo,checkTime,checkDate,id) values(?,?,?,?,?)"
        dbTools.batchInsert(sql, valuesList)
        dbTools.close()
        logger.info(f"成功导入打卡机数据,行数{count}")
        return count
    except Exception as ex:
        logging.exception(ex)
        return None


# 导入请假数据
def handleRestData(file):
    try:
        utils = excelUtils.excelTools()
        res = utils.loadRestExcel(file)
        valuesList = []
        count = 0
        for i in range(res.__len__()):
            count += 1
            res[i].append(None)
            valuesList.append(tuple(res[i]))
        dbTools = dbUtils.dbTools()
        # 清除历史请假数据
        dbTools.clear('t_restdata')
        # 插入数据库
        sql = "INSERT INTO t_restdata(applyperson,leavetype,started ,finished ,leavetime ,leavereason,id) values(?,?,?,?,?,?,?)"
        dbTools.batchInsert(sql, valuesList)
        dbTools.close()
        logger.info(f"成功导入请假数据,行数{count}")
        return count
    except Exception as ex:
        logging.exception(ex)
        return None


# 获取打卡日期
def dataFormat(checkDate) -> string:
    data = time.strptime(checkDate, '%Y-%m-%d %H:%M:%S')
    str = time.strftime('%Y-%m-%d', data)
    return str


# 保存配置数据
def saveSetupData(dataList, dType):
    valuesList = []
    count = 0
    try:
        for i in range(dataList.__len__()):
            count += 1
            dataList[i].append(None)
            valuesList.append(tuple(dataList[i]))

        dbTools = dbUtils.dbTools()
        # 清除历史配置数据
        delSql = 'DELETE FROM t_setup where dtype = ?'
        dbTools.execute(delSql, [dType])
        # 插入新数据
        sql = "INSERT INTO t_setup(content,dtype,id) values(?,?,?)"
        dbTools.batchInsert(sql, valuesList)
        dbTools.close()
        logger.info(f"成功导入设置数据,行数{count}")
        return count
    except Exception as ex:
        logging.exception(ex)
        return None


def querySetupData() -> list:
    sql = 'select * from t_setup'
    dbTools = dbUtils.dbTools()
    list = dbTools.execute(sql)
    logger.debug(list)
    dbTools.close()
    return list


# 处理数据，导出excel
def outputResult() -> bool:
    try:
        # 初始化所有考勤数据
        __initResultTable()
        # 初始化特殊日期
        __initSetUp()
        # 补充打卡记录信息
        __supplementCheckdata()
        # 补充请假数据
        __supplementRestdata()
        # 标记迟到、计算加班
        __supplementLaterData()
        __supplementOutTimeData()
        logger.info("迟到、加班计算完成")
        __outputExcel()
        return True
    except Exception as ex:
        logging.exception(ex)
        return False


# 数据初始化
def __initResultTable():
    dbTools = dbUtils.dbTools()
    # 清理历史数据
    dbTools.clear("t_results")
    # 获取月份
    sql = "SELECT checkDate FROM t_checkdata c GROUP BY c.checkDate ORDER BY checkDate LIMIT 1"
    resList = dbTools.execute(sql)
    dataStr = resList[0]['checkDate']
    data = time.strptime(dataStr, '%Y-%m-%d')
    days = calendar._monthlen(data.tm_year, data.tm_mon)
    # 获取人员列表
    sql = 'SELECT name,checkNo FROM t_checkdata c GROUP BY c.name ORDER BY checkNo'
    resList = dbTools.execute(sql)
    values = []
    for row in resList:
        for i in range(1, days + 1):
            temp = [None] + list(row.values())
            strData = str(data.tm_year) + '-' + str(data.tm_mon) + '-' + str(i)
            data = time.strptime(strData, '%Y-%m-%d')
            strData = time.strftime('%Y-%m-%d', data)
            temp.append(strData)
            # 初始化工作日=1 假期=2
            index = datetime.datetime.strptime(strData, '%Y-%m-%d').weekday() + 1
            temp.append(index)
            if index >= 6:
                temp.append(2)
            else:
                temp.append(1)
            temp.append(0)
            values.append(tuple(temp))

    # 保存
    # 插入新数据
    sql = "INSERT INTO t_results(id,name,checkNo,checkDate,dayofweek,dtype,subsidies) values(?,?,?,?,?,?,?)"
    dbTools.batchInsert(sql, values)
    dbTools.close()
    logger.info("数据初始化成功！")


# 特殊日期初始化
def __initSetUp():
    dbTools = dbUtils.dbTools()
    # 获取配置日期
    sql = "SELECT content FROM t_setup WHERE dtype=?"
    upSql = "UPDATE t_results SET dtype=? where checkDate in ("
    # 工作日 = 1  假期 = 2   人员 = 3
    workDays = dbTools.execute(sql, [1])
    if workDays is not None:
        temp = []
        for day in workDays:
            temp.append(day["content"])
        t = tuple(temp)
        s = "','"
        s = s.join(t)
        upSql = upSql + "'" + s + "')"
        dbTools.execute(upSql, [1])

    upSql = "UPDATE t_results SET dtype=? where checkDate in ("
    holidays = dbTools.execute(sql, [2])
    if holidays is not None:
        temp = []
        for day in holidays:
            temp.append(day["content"])
        t = tuple(temp)
        s = "','"
        s = s.join(t)
        upSql = upSql + "'" + s + "')"
        dbTools.execute(upSql, [2])
    dbTools.close()


# 补充打卡数据
def __supplementCheckdata():
    dbTools = dbUtils.dbTools()
    # 清理历史数据
    dbTools.clear("t_temp")
    sql_insert = "INSERT INTO t_temp ( id, name, checkNo, started, finished, checkDate ) SELECT c.id, c.name, c.checkNo, min( c.checkTime ) up, max( c.checkTime ) down, c.checkDate FROM 	t_checkdata c GROUP BY c.checkNo,c.checkDate"
    dbTools.execute(sql_insert)
    sql_update = "UPDATE t_results SET started = (SELECT started FROM t_temp where t_results.checkNo=t_temp.checkNo AND t_results.checkDate=t_temp.checkDate LIMIT 1) ,finished = (SELECT finished FROM t_temp where t_results.checkNo=t_temp.checkNo AND t_results.checkDate=t_temp.checkDate LIMIT 1) WHERE exists(select * from t_temp where t_results.checkNo=t_temp.checkNo AND t_results.checkDate=t_temp.checkDate);"
    dbTools.execute(sql_update)
    dbTools.close()
    logger.info("补充打卡数据完成！")


# 补充请假数据
def __supplementRestdata():
    dbTools = dbUtils.dbTools()
    sql = "select * from t_restdata"
    restData = dbTools.execute(sql)
    dbTools.close()
    for data in restData:
        if data["leavetime"] <= 1.0:
            __updateRest(data)
        else:
            # 多天
            rows = __buildRows(data)
            for row in rows:
                __updateRest(row)
    logger.info("补充请假数据完成！")


def __buildRows(data):
    days = data["leavetime"]
    rows = []

    # 上午开始请假
    if data["started"].find('08:30:00') >= 0:
        started = data["started"]
        while days > 0:
            split = copy.deepcopy(data)
            if days >= 1:
                # 全天假
                split["started"] = started
                split["finished"] = started.replace('08:30:00', '18:00:00')
                split["leavetime"] = 1.0

            else:
                # 构造半天
                split["started"] = started
                split["finished"] = started.replace('08:30:00', '12:00:00')
                split["leavetime"] = 0.5
            rows.append(split)
            dt = datetime.datetime.strptime(started, '%Y-%m-%d %H:%M:%S')
            started = (dt + datetime.timedelta(days=1)).strftime('%Y-%m-%d %H:%M:%S')
            days -= 1

    else:
        #  下午开始请假
        started = data["started"]
        # 构造半天
        split = copy.deepcopy(data)
        split["started"] = started
        split["finished"] = started.replace('12:00:00', '18:00:00')
        split["leavetime"] = 0.5
        rows.append(split)
        days = days - 0.5
        finished = split["finished"]
        dt = datetime.datetime.strptime(finished, '%Y-%m-%d %H:%M:%S')
        finished = (dt + datetime.timedelta(days=1)).strftime('%Y-%m-%d %H:%M:%S')
        while days > 0.5:
            split = copy.deepcopy(data)
            if days >= 1:
                # 全天假
                split["started"] = finished.replace('18:00:00', '08:30:00')
                split["finished"] = finished
                split["leavetime"] = 1.0
                rows.append(split)
            dt = datetime.datetime.strptime(finished, '%Y-%m-%d %H:%M:%S')
            finished = (dt + datetime.timedelta(days=1)).strftime('%Y-%m-%d %H:%M:%S')
            days -= 1
    return rows


def __updateRest(data):
    dbTools = dbUtils.dbTools()
    if data is not None:
        if data["leavetime"] < 1.0:
            if data["started"].find('08:30:00') >= 0:
                # 上午半天
                sql = 'UPDATE t_results SET started=?, leavetype=?,leavetime=? WHERE name=? AND checkDate=?'
                values = []
                values.append(data["started"])
                values.append(data["leavetype"])
                values.append(data["leavetime"])
                values.append(data["applyperson"])
                checkData = data["started"]
                values.append(checkData[0:checkData.find(' 08:30:00')])
                dbTools.execute(sql, values)

            if data["started"].find('12:00:00') >= 0:
                # 下午半天
                sql = 'UPDATE t_results SET finished=?, leavetype=?,leavetime=? WHERE name=? AND checkDate=?'
                values = []
                values.append(data["finished"])
                values.append(data["leavetype"])
                values.append(data["leavetime"])
                values.append(data["applyperson"])
                checkData = data["started"]
                values.append(checkData[0:checkData.find(' 12:30:00')])
                dbTools.execute(sql, values)
        elif data["leavetime"] == 1.0:
            # 一天
            sql = 'UPDATE t_results SET started=?, finished=?, leavetype=?,leavetime=? WHERE name=? AND checkDate=?'
            values = []
            values.append(data["started"])
            values.append(data["finished"])
            values.append(data["leavetype"])
            values.append(data["leavetime"])
            values.append(data["applyperson"])
            checkData = data["started"]
            values.append(checkData[0:checkData.find(' 08:30:00')])
            dbTools.execute(sql, values)
        else:
            logging.error("数据异常")
    dbTools.close()


def __supplementLaterData():
    dbTools = dbUtils.dbTools()
    sql = "UPDATE t_results SET laterTimes=1 where TIME(started)>TIME('08:36:00') AND dtype=1"
    dbTools.execute(sql)
    dbTools.close()


def __personRemove(name, list) -> bool:
    if list is not None:
        for person in list:
            if person == name:
                return True
    return False


def __isLastFriday(checkDate) -> bool:
    data = time.strptime(checkDate, '%Y-%m-%d')
    days = calendar._monthlen(data.tm_year, data.tm_mon)
    day = data.tm_mday
    if day + 7 <= days:
        return False
    else:
        return True


def __supplementOutTimeData():
    dbTools = dbUtils.dbTools(falg=False)
    setup_sql = 'select * from t_setup s where s.dtype=3'
    personList = dbTools.execute(setup_sql)
    dbTools.close()

    dbTools = dbUtils.dbTools()
    # 工作日加班
    sql = "SELECT r.id,r.name,r.checkDate,r.dayofweek,r.finished FROM t_results r where TIME(r.finished)>TIME('19:30:00') AND r.dtype=1"
    rows = dbTools.execute(sql)

    if rows is not None:
        values = []
        for row in rows:
            if row["dayofweek"] == 5:
                # 该人员是否在排除列表中，需要跳过
                if __personRemove(row["name"], personList):
                    continue
                # 所有人月末周五不计算加班
                if __isLastFriday(row["checkDate"]):
                    continue
            value = []
            h = __computeOutTime(row["finished"])
            value.append(h)
            if h >= 1:
                value.append(1)
            else:
                value.append(0)
            value.append(row["id"])
            values.append(value)
        upSql = 'UPDATE t_results SET outTime=?,subsidies=? WHERE id=?'
        dbTools.batchUpdate(upSql, values)
    # 节假日加班
    # 节假日加班超3小时 补贴1
    upSql1 = 'UPDATE t_results set subsidies=1 WHERE id in (SELECT r.id FROM t_results r where r.dtype=2 AND (r.leavetime is null or r.leavetime<=0.5) AND (julianday(r.finished)-julianday(r.started))*24 >=3);'
    # 节假日 加班时长
    upSql2 = 'UPDATE t_results set outTime = ROUND((julianday(finished)-julianday(started))*24) WHERE id in (SELECT r.id FROM t_results r where r.dtype=2 AND (r.leavetime is null or r.leavetime<=0.5) AND (julianday(r.finished)-julianday(r.started))*24*60 > 40);'
    upSql3 = 'UPDATE t_results set outTime = 8 WHERE outTime > 8;'

    dbTools.execute(upSql1)
    dbTools.execute(upSql2)
    dbTools.execute(upSql3)
    dbTools.close()


def __computeOutTime(startTime, endTime='19:00:00'):
    stime = datetime.datetime.strptime(startTime, '%Y-%m-%d %H:%M:%S')
    etime = datetime.datetime.strptime(endTime, '%H:%M:%S')
    seconds = (stime - etime).seconds
    count = seconds / 60
    h = int(count / 60)
    m = int(count % 60)
    if h >= 1:
        if m >= 30:
            h = h + 0.5
    else:
        if m > 40:
            h = h + 0.5

    return h


def __outputExcel():
    dbTools = dbUtils.dbTools(falg=False)
    sum_sql = '''
        SELECT t.checkNo,t.name,t.days,t1.leavetype,t1.leavetime,t.laterTimes,t.outTime,t.subsidies,
         case when (t.days - leavetime) is null then t.days else t.days - leavetime end days1
         FROM 
            (SELECT
                r.checkNo,
                r.name,
                count( * ) days,
                sum(r.laterTimes) laterTimes,
                sum(r.outTime) outTime,
                sum(subsidies) subsidies
            FROM
                t_results r 
            WHERE
                dtype=1 
            GROUP BY
                r.checkNo) t
            LEFT JOIN
            (
            SELECT 
                r.checkNo,
                SUM(r.leavetime) leavetime,
                r.leavetype
            FROM
                t_results r
            WHERE
                dtype=1 AND leavetype is not null
            GROUP BY
                r.checkNo,r.leavetype
                ) t1 ON t1.checkNo = t.checkNo

    '''
    dea_sql = '''
        SELECT
            r.checkNo,
            r.name,
            r.checkDate,
            c.started,
            c.finished,
            r.laterTimes,
            r.leavetype,
            r.leavetime,
            r.outTime,
            r.subsidies
        FROM
            t_results r
            LEFT JOIN t_temp c ON r.checkNo = c.checkNo 
            AND r.checkDate = c.checkDate 
    '''
    summary = dbTools.execute(sum_sql)
    details = dbTools.execute(dea_sql)
    dbTools.close()

    utils = excelUtils.excelTools()
    utils.outputExcel(summary, details)
    pass
